package ims.db.view.customer.order;

import ims.beans.Address;
import ims.beans.Customer;
import ims.beans.CustomerOrder;
import ims.beans.Payment;
import ims.beans.Product;
import ims.db.AbstractDAO;
import ims.db.AbstractDbRequest;
import ims.db.AbstractDbResponse;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ViewCustomerOrderDAO extends AbstractDAO {

	public ViewCustomerOrderDAO() throws ClassNotFoundException, SQLException {
		super();
	}

	@Override
	public AbstractDbResponse execute(AbstractDbRequest dbRequest) 
	{
		ViewCustomerOrderDbResponse response = new ViewCustomerOrderDbResponse();
		ViewCustomerOrderDbRequest request = (ViewCustomerOrderDbRequest)dbRequest;
        try 
        {
        	Statement stmt = conn.createStatement();
        	StringBuffer select= new StringBuffer("Select co.invoiceNum, u.firstname,u.lastname, u.email, to_char(co.orderdate,'MM-DD-YYYY'), co.ordertype ");
        	select.append(", ship.firstName, ship.lastName, ship.street, ship.city, ship.state, ship.zip  ");
        	select.append(", bill.firstName, bill.lastName, bill.street, bill.city, bill.state, bill.zip  ");
        	select.append(", p.paymentType, p.ccNum, p.expirationDate ");
        	select.append(", prod.pid, prod.price, prod.prodDesc, co.quantity ");
        	String from = " from customerorder co, useraccount u, Address bill, Address ship, Payment p, Product prod";
        	StringBuffer where = new StringBuffer(" where co.userid=u.userid and co.pid=prod.pid and co.shippingAid = ship.aid and co.billingAid=bill.aid and co.paymentid=p.paymentId ");
        	where.append(" and co.invoiceNum='").append(request.getOrderNum()).append("'");
        	String sql = select + from + where;
        	
        	ResultSet rset = stmt.executeQuery(sql);
        	CustomerOrder order = new CustomerOrder();
        	rset.next();
        	
    		order.setOrderNum(rset.getString(1));
    		Customer customer = new Customer();
    		customer.setName(rset.getString(2)+" "+ rset.getString(3));
    		customer.setEmail(rset.getString(4));
    		order.setCustomer(customer);
    		
    		order.setOrderDt(rset.getString(5));
    		order.setOrderType(rset.getString(6));
    		
    		Address shipping = new Address();
    		shipping.setFirstNm(rset.getString(7));
    		shipping.setLastNm(rset.getString(8));
    		shipping.setStreet(rset.getString(9));
    		shipping.setCity(rset.getString(10));
    		shipping.setState(rset.getString(11));
    		shipping.setZip(rset.getInt(12));
    		order.setShippingAdd(shipping);
    		
    		Address billing = new Address();
    		billing.setFirstNm(rset.getString(13));
    		billing.setLastNm(rset.getString(14));
    		billing.setStreet(rset.getString(15));
    		billing.setCity(rset.getString(16));
    		billing.setState(rset.getString(17));
    		billing.setZip(rset.getInt(18));
    		order.setBillingAdd(billing);
    		
    		Payment pmt = new Payment();
    		pmt.setPaymentType(rset.getString(19));
    		pmt.setCcNum(rset.getString(20));
    		pmt.setExpDt(rset.getString(21));
    		order.setPayment(pmt);
    		
    		ArrayList <Product> products = new ArrayList();
    		Product product = new Product();
    		product.setPid(rset.getString(22));
    		product.setPrice(rset.getDouble(23));
    		product.setProdDesc(rset.getString(24));
    		product.setQty(rset.getInt(25));
    		products.add(product);
    		
        	while(rset.next())
            {
        		Product prod = new Product();
        		prod.setPid(rset.getString(22));
        		prod.setPrice(rset.getDouble(23));
        		prod.setProdDesc(rset.getString(24));
        		prod.setQty(rset.getInt(25));
        		products.add(prod);
        	}
        	order.setProducts(products);
        	
        	response.setOrders(order);
        	
	        rset.close();
            stmt.close();
            conn.close();
        } 
        catch (SQLException sqlEx)
        {
        	sqlEx.printStackTrace();
        }
        return response;
	}
}
